<?php

namespace app\admin\controller;

use app\common\controller\Backend;
use app\common\model\Level as LevelModel;
use fast\Tree;
use think\Console;
use think\Db;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

/**
 * 分类管理
 *
 * @icon   fa fa-list
 * @remark 用于统一管理网站的所有分类,分类可进行无限级分类,分类类型请在常规管理->系统配置->字典配置中添加
 */
class Project extends Backend
{

    /**
     * @var \app\common\model\Category
     */
    protected $model = null;
    protected $categorylist = [];
    protected $noNeedRight = ['selectpage'];

    public function _initialize()
    {
        parent::_initialize();
        $this->model = model('app\common\model\Project');

        //获取一级板块
        $ministries = db("ministries")->select();
        $this->view->assign("ministries", $ministries);
        //获取二级板块
        $cateList = db("category")->order("id dese")->select();
        $this->view->assign("cateList", $cateList);
        //建设主体
        $subject = db("subject")->select();
        $this->view->assign("subject", $subject);
    }


    /**
     * 查看
     */
    public function index()
    {
        //当前是否为关联查询
        $this->relationSearch = true;
        //设置过滤方法
        $this->request->filter(['strip_tags', 'trim']);
        if ($this->request->isAjax())
        {
            //如果发送的来源是Selectpage，则转发到Selectpage
            if ($this->request->request('keyField')) {
                return $this->selectpage();
            }


            list($where, $sort, $order, $offset, $limit) = $this->buildparams("number,title");
            $total = $this->model
                ->with(['ministries','cate','subject','payment'])
                ->where($where)
                ->order("fa_project.id asc")
                ->count();

            $list = $this->model
                ->with(['ministries','cate','subject','payment'])
                ->where($where)
                ->order("fa_project.id asc")
                ->limit($offset, $limit)
                ->select();


            foreach ($list as $row) {

                $row->getRelation('ministries')->visible(['m_name']);
                $row->getRelation('cate')->visible(['name']);
                $row->getRelation('subject')->visible(['subject']);

            }

            $list = collection($list)->toArray();
            $result = array("total" => $total, "rows" => $list);
            return json($result);

        }
        return $this->view->fetch();
    }

    //处理新增
    public function doadd(){
        if ($this->request->isPost()) {
            $params = $this->request->post("row/a");

            if ($params) {
                $params = $this->preExcludeFields($params);

                try {
                    Db::startTrans();
                    $now = time();

                    $mname = preg_replace('# #', '', $params['title']);

                    $has = db("project")->where(['title'=>$mname])->find();

                    if($has){
                        $this->error("具体项目名称已经存在");
                    }else{

                        $params['title'] = $mname;//保存去除空格后的名称，防止下次查询不到
                        //判断阶段目标是否合法
                        //结束日期-开始日期 动态计算汇报次数

                        if($params['prostartdate']>= $params['proenddate']){
                            $this->error("项目结束日期必须大于开始日期");
                        }

                  /*      //粗略计算 汇报次数
                        $countarr = diffDate($params['proenddate'],$params['prostartdate']);
                        $count = ceil($countarr['m']/2);
                        $params['count'] = $count;*/

                        //判断资金来源
                        $payment = $this->request->post("pm/a");

                        if(empty($payment['payment']) && empty($payment['expense'] && empty($payment['other']))){
                            $this->error("资金来源不能全部为空或0");
                        }


                         $payment['ctime'] = $now;
                         $payment['unit_id'] = $params['unit_id'];
                         $payment['a_id'] = $params['a_id'];

                         $pid = db("payment")->insertGetId($payment);
                         $params['ctime'] = $now;
                         $params['p_id'] = $pid;
                         $prid = db("project")->insertGetId($params);

                        if($pid && $prid){
                            Db::commit();
                            $this->success();
                        }else{
                            Db::rollback();
                            $this->error("添加失败");
                        }
                    }

                } catch (\think\exception\PDOException $e) {
                    $this->error($e->getMessage());
                } catch (\think\Exception $e) {
                    $this->error($e->getMessage());
                }
            }
            $this->error(__('Parameter %s can not be empty', ''));
        }
    }

    /**
     * 编辑
     */
    public function edit($ids = null)
    {


        $row = $this->model->get($ids);
        if (!$row) {
            $this->error(__('No Results were found'));
        }
        $adminIds = $this->getDataLimitAdminIds();
        if (is_array($adminIds)) {
            if (!in_array($row[$this->dataLimitField], $adminIds)) {
                $this->error(__('You have no permission'));
            }
        }
        if ($this->request->isPost()) {
            $params = $this->request->post("row/a");
            if ($params) {
                $params = $this->preExcludeFields($params);

                try {
                    //是否采用模型验证
                    if ($this->modelValidate) {
                        $name = str_replace("\\model\\", "\\validate\\", get_class($this->model));
                        $validate = is_bool($this->modelValidate) ? ($this->modelSceneValidate ? $name . '.edit' : $name) : $this->modelValidate;
                        $row->validate($validate);
                    }


                    Db::startTrans();//开启事务 一点要修改表引擎为 innodb

                    $now = time();
                    $mname = preg_replace('# #', '', $params['title']);

                    $has = db("project")->where(['title'=>$mname])->where("id != $ids")->find();

                    if($has){
                        $this->error("具体项目名称已经存在");
                    }else{

                        $params['title'] = $mname;//保存去除空格后的名称，防止下次查询不到

                        //判断阶段目标是否合法
                        //结束日期-开始日期 动态计算汇报次数

                        if($params['prostartdate']>= $params['proenddate']){
                            $this->error("项目结束日期必须答应开始日期");
                        }

                        //粗略计算 汇报次数
                        $countarr = diffDate($params['proenddate'],$params['prostartdate']);
                        $count = ceil($countarr['m']/2);
                        $params['count'] = $count;



                        //判断资金来源
                        $payment = $this->request->post("pm/a");

                        if(empty($payment['payment']) && empty($payment['expense'] && empty($payment['other']))){
                            $this->error("资金来源不能全部为空或0");
                        }

                        $payment['utime'] = $now;

                        //更新资金
                        $eff = db("payment")->where(['id'=>$row['p_id']])->update($payment);
                        $params['utime'] = $now;
                    }

                    $result = $row->allowField(true)->save($params);

                    if ($result !== false && $eff) {
                        Db::commit();
                        $this->success();
                    } else {
                        Db::rollback();
                        $this->error($row->getError());
                    }


                } catch (\think\exception\PDOException $e) {
                    $this->error($e->getMessage());
                } catch (\think\Exception $e) {
                    $this->error($e->getMessage());
                }
            }
            $this->error(__('Parameter %s can not be empty', ''));
        }

        //获取资金来源数据
        $payment = db("payment")->where(["id"=>$row['p_id']])->field("payment,expense,ratio,other")->find();

        $this->view->assign("row", $row);
        $this->view->assign("payment", $payment);
        return $this->view->fetch();
    }

    /**
     * Selectpage搜索
     *
     * @internal
     */
    public function selectpage()
    {
        return parent::selectpage();
    }

    //数据校验 严格校验 错误回滚
    public function check(){

        if (empty($this->request->post('file'))) {
            $this->error('请上传文件');
        }
        vendor('phpexcel.PHPExcel');
        $filePath = $this->request->post('file');

        $arr = explode('.', $filePath);
        if ($arr[count($arr) - 1] != 'xlsx' && $arr[count($arr) - 1] != 'xls') {
            $this->error('文件格式不正确');
        }

        //清空历史校验数据
        db("projecterror")->execute("TRUNCATE fa_projecterror");


        $objreder = \PHPExcel_IOFactory::load(ROOT_PATH . DS . 'public' . DS . $filePath);
        $sheet = $objreder->getActiveSheet();
        $merge = $sheet->getMergeCells();//获取合并的单元格数据
        $varchar = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z",
            "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ"];
        //取列合并数据
        $mergeColumn = [];//列合并
        $mergeRow = [];//行合并
        if ($merge) {
            foreach ($merge as $cells) {
                $str = explode(':', $cells);
                if (count($str) >= 2) {
                    if (substr($str[0], 0, 1) == substr($str[1], 0, 1)) {//代表列合并
                        $mergeColumn[] = $cells;
                    } else {
                        $mergeRow[] = $cells;
                    }
                }
            }
        }
        $sheetCount = $objreder->getSheetCount();//获取excel文件里有多少个sheet

        //这里写死只读sheet1
        for ($i = 0; $i < 1; $i++) {
            $data = $objreder->getSheet($i)->toArray();//读取每个sheet里的数据 全部放入到数组中
        }

        //暂时校验 具体项目名称和建设主体不能为空 名称不能重复
        $errordata = [];
        $sucdata = [];
        $now = time();
        $len = count($data);
        $nowyear = date("Y",$now);

        for ($j=6;$j<$len;$j++){
            if (is_string($data[$j][0]) && strlen(trim($data[$j][0])) >= 6) {//粗略判断是否为填报单位
                continue;
            } else {
                //校验 项目名称
                $remark = "";
                $error = [];
                $title = preg_replace('# #', '', $data[$j][1]);
                if(empty($title)){
                    $error['number'] = "项目编号：".$data[$j][0];
                    $error['title'] = "";
                    $remark .= "具体项目名称：不能为空|";
                    $error['ctime'] = $now;
                }else{
                    //判断 名称是否重复
                    if(in_array($title,$sucdata)){
                        $error['number'] = "项目编号：".$data[$j][0];
                        $error['title'] = "";
                        $remark .= "具体项目名称：不能重复|";
                        $error['ctime'] = $now;
                    }else{
                        array_push($sucdata,$title);
                    }

                }

                //校验 建设主体
                $subject = preg_replace('# #', '', $data[$j][6]);

                if(empty($subject)){
                    $error['number'] = "项目编号：".$data[$j][0];
                    $error['title'] = $title;
                    $remark .= "建设主体：不能为空|";
                    $error['ctime'] = $now;
                }

                // 校验项目（计划）完成时间
                $proenddate = preg_replace('# #', '', $data[$j][11]);
                if(empty($proenddate)){
                    $error['number'] = "项目编号：".$data[$j][0];
                    $error['title'] = $title;
                    $remark .= "项目(计划)完成时间：不能为空|";
                    $error['ctime'] = $now;
                }

                //校验日期格式 必须是年和月
                $cyear =  substr_count($proenddate,'年');
                if($cyear != 1){
                    $error['number'] = "项目编号：".$data[$j][0];
                    $error['title'] = $title;
                    $remark .= "项目(计划)完成时间：时间不合法、必须包含年|";
                    $error['ctime'] = $now;
                }
                $cmonth =  substr_count($proenddate,'月');
                if($cmonth != 1){
                    $error['number'] = "项目编号：".$data[$j][0];
                    $error['title'] = $title;
                    $remark .= "项目(计划)完成时间：时间不合法、必须包含月|";
                    $error['ctime'] = $now;
                }

                //校验项目开始时间
                $prostartdate = preg_replace('# #', '', $data[$j][12]);
                if(empty($prostartdate)){
                    $error['number'] = "项目编号：".$data[$j][0];
                    $error['title'] = $title;
                    $remark .= "项目开始时间：不能为空|";
                    $error['ctime'] = $now;
                }
                //校验日期格式 必须是年和月
                $cyear1 =  substr_count($prostartdate,'年');
                if($cyear1 != 1){
                    $error['number'] = "项目编号：".$data[$j][0];
                    $error['title'] = $title;
                    $remark .= "项目开始时间：时间不合法、必须包含年|";
                    $error['ctime'] = $now;
                }
                $cmonth2 =  substr_count($prostartdate,'月');
                if($cmonth2 != 1){
                    $error['number'] = "项目编号：".$data[$j][0];
                    $error['title'] = $title;
                    $remark .= "项目开始时间：时间不合法、必须包含月|";
                    $error['ctime'] = $now;
                }
                //时间比较
                $proenddatetmp = str_replace("年","-",$proenddate);
                $proenddatetmp = str_replace("月","-",$proenddatetmp);
                $proenddatetmp = rtrim($proenddatetmp,"-");
                $proenddate = $proenddatetmp;

                $proenddateint  = strtotime($proenddate);
                $oldyear = date("Y",$proenddateint);
                if($oldyear < $nowyear){
                    $error['number'] = "项目编号：".$data[$j][0];
                    $error['title'] = $title;
                    $remark .= "项目(计划)完成时间：不能小于今年|";
                    $error['ctime'] = $now;
                }

                $prostartdatetmp = str_replace("年","-",$prostartdate);
                $prostartdatetmp = str_replace("月","-",$prostartdatetmp);
                $prostartdatetmp = rtrim($prostartdatetmp,"-");
                $prostartdate = $prostartdatetmp;
                $prostartdateint  = strtotime($prostartdate);

                //校验日期大小
                if($prostartdateint >= $proenddateint){
                    $error['number'] = "项目编号：".$data[$j][0];
                    $error['title'] = $title;
                    $remark .= "项目结束时间必须大于开始时间";
                    $error['ctime'] = $now;
                }

                //总金额不能为空
                $investment =  preg_replace('# #', '', $data[$j][17]);
                if(empty($investment)){
                    $error['number'] = "项目编号：".$data[$j][0];
                    $error['title'] = $title;
                    $remark .= "总投资金额：不能为空|";
                    $error['ctime'] = $now;
                }

                //资金来源
                $zj1 = preg_replace('# #', '', $data[$j][18]);//自筹
                $zj2 = preg_replace('# #', '', $data[$j][19]);//转移
                $zj3 = preg_replace('# #', '', $data[$j][20]);//转移支付

                if(empty($zj1) && empty($zj2) && empty($zj3)){
                    $error['number'] = "项目编号：".$data[$j][0];
                    $error['title'] = $title;
                    $remark .= "资金来源：三种资金来源不能同时都为空|";
                    $error['ctime'] = $now;
                }

                if($remark){
                    $error['remark'] = $remark;
                    //$error['remark'] = substr($remark,0,strlen($remark)-1);
                }

            }
            if(count($error) > 0){
                $errordata[] = $error;
            }

        }

        if($errordata){
            $isid = db("projecterror")->insertAll($errordata);
            $this->error("数据有误，请核实修改后重新校验");
        }else{
            $this->success("校验成功，请进行数据导入");
        }

    }

    //基础数据导入 严格校验 全部正确方可导入import
    public function import()
    {

        ini_set('max_execution_time','0');

        if (empty($this->request->post('file'))) {
            $this->error('请上传文件');
        }
        vendor('phpexcel.PHPExcel');
        $filePath = $this->request->post('file');

        $arr = explode('.', $filePath);
        if ($arr[count($arr) - 1] != 'xlsx' && $arr[count($arr) - 1] != 'xls') {
            $this->error('文件格式不正确');
        }
        $objreder = \PHPExcel_IOFactory::load(ROOT_PATH . DS . 'public' . DS . $filePath);
        $sheet = $objreder->getActiveSheet();
        $merge = $sheet->getMergeCells();//获取合并的单元格数据
        $varchar = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z",
            "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ"];
        //取列合并数据
        $mergeColumn = [];//列合并
        $mergeRow = [];//行合并
        if ($merge) {
            foreach ($merge as $cells) {
                $str = explode(':', $cells);
                if (count($str) >= 2) {
                    if (substr($str[0], 0, 1) == substr($str[1], 0, 1)) {//代表列合并
                        $mergeColumn[] = $cells;
                    } else {
                        $mergeRow[] = $cells;
                    }
                }
            }
        }
        $sheetCount = $objreder->getSheetCount();//获取excel文件里有多少个sheet

        //清空历史校验数据
        db("projecterror")->execute("TRUNCATE fa_projecterror");



        $unit = '';//填表单位 1-7个
        $unit_id = 0;
        $assignment_id = 0; //23项目任务表
        $proof = ''; //具体条文依据
        //这里写死只读sheet1
        for ($i = 0; $i < 1; $i++) {
            $data = $objreder->getSheet($i)->toArray();//读取每个sheet里的数据 全部放入到数组中
        }

        //外层事务 要么都成功，要么都失败，单条成功也不会的导入。
        Db::startTrans();
        try {
            $jj = 0;
            for ($j = 6; $j < count($data); $j++) {

                if (is_string($data[$j][0]) && strlen(trim($data[$j][0])) >= 6) {//粗略判断是否为填报单位
                    $unit = preg_replace('# #', '', trim($data[$j][0]));//获取新单位
                    //判断新单位是否存在,不存在存入数据库单位表
                    $units = Db::table('fa_ministries')->field("id")->where(['m_name'=>$unit])->find();
                    if ($units) {
                        $unit_id = $units['id'];
                    } else {
                        $unit_id = Db::table('fa_ministries')->insertGetId(['m_name' => $unit, 'ctime' => time()]);
                    }
                } else {

                    if (!$unit) {
                        continue;//新单位不存在跳过本次循环
                    }

                    $details = Db::table('fa_project')->where('title', preg_replace('# #', '', $data[$j][1]))->where('title', 'neq', '')->find();

                    if ($details) {
//                        continue;//任务编号存在则跳过本次循环
                        $isDetails = true;
                    } else {
                        $jj ++;
                        $isDetails = false;
                    }

                    if ($data[$j][2]) {
                        //23项目任务
                        $assignment = preg_replace('# #', '', $data[$j][2]);
                        //判断任务是否存在,不存在存入数据库任务表
                        $assignments = Db::table('fa_category')->where(['pid'=>$unit_id,'name'=>$assignment])->find();
                        if ($assignments) {
                            $assignment_id = $assignments['id'];
                        } else {
                            $assignment_id = Db::table('fa_category')->insertGetId(['pid' => $unit_id, 'name' => $assignment, 'createtime' => time()]);
                        }
                    } /*else {
                        //判断是否为合并单元格，预留
                        $isRange = false;
                        foreach ($merge as $cells) {
                            $cell = $sheet->getCell($varchar[1] . ($j + 1));
                            if ($cell->isInRange($cells)) {
                                $isRange = true;
                            }
                        }
                        if (!$isRange) {
                            $assignment_id = 0;
                        }
                    }*/
                    //责任主体表
                    $subject = preg_replace('# #', '', $data[$j][6]);
                    if ($subject) {
                        $subjects = Db::table('fa_subject')->where('subject', $subject)->find();
                        if ($subjects) {
                            $subject_id = $subjects['id'];
                        } else {
                            $subject_id = Db::table('fa_subject')->insertGetId(['subject' => $subject, 'ctime' => time()]);
                        }
                    } else {
                        $subject_id = 0;
                    }

                    //具体条文依据
                    if ($data[$j][3]) {
                        $proof = preg_replace('# #', '', $data[$j][3]);
                    }

                    //任务详情表
                    $j2 = $data[$j][0];
                    if($j2 <=9){
                        $jj2 = "00".$j2;
                    }elseif ($j2 >9 && $j2 <=99){
                        $jj2 = "0".$j2;
                    }else{
                        $jj2 = $j2;
                    }

                    $detail['number'] = $jj2;
                    $detail['enumber'] = $data[$j][0];
                    $detail['unit_id'] = $unit_id;
                    $detail['a_id'] = $assignment_id;
                    $detail['proof'] = $proof;
                    $detail['title'] = preg_replace('# #', '', $data[$j][1]);//具体项目名称
                    $detail['survey'] = preg_replace('# #', '', $data[$j][4]);//项目概况
                    $detail['subject'] = $subject_id;
                    $detail['innovation'] = preg_replace('# #', '', $data[$j][5]);//融合创新意义
                    $detail['ministries'] = preg_replace('# #', '', $data[$j][7]);//国家部委
                    $detail['municipal'] = preg_replace('# #', '', $data[$j][8]);//市级单位
                    $detail['contractor'] = preg_replace('# #', '', $data[$j][9]);//甲方
                    $detail['track'] = preg_replace('# #', '', $data[$j][10]);//跟踪服务处
                    //时间处理 结束时间
                    $proenddate = preg_replace('# #', '', $data[$j][11]);
                    $proenddatetmp = str_replace("年","-",$proenddate);
                    $proenddatetmp = str_replace("月","-",$proenddatetmp);
                    $proenddatetmp = rtrim($proenddatetmp,"-");
                    $proenddate = date("Y-m", strtotime($proenddatetmp));
                    $detail['proenddate'] =  $proenddate; //项目（计划）完成时间
                    //开始时间
                    $prostartdate = preg_replace('# #', '', $data[$j][12]);
                    $prostartdatetmp = str_replace("年","-",$prostartdate);
                    $prostartdatetmp = str_replace("月","-",$prostartdatetmp);
                    $prostartdatetmp = rtrim($prostartdatetmp,"-");
                    $prostartdate = date("Y-m", strtotime($prostartdatetmp));
                    $detail['prostartdate'] = $prostartdate;//项目开始日期
                    $detail['yeartarget'] = preg_replace('# #', '', $data[$j][13]);//本年度任务目标 动态年 计算的百分比
                    $detail['assessment'] = preg_replace('# #', '', $data[$j][14]);//阶段总结评估（每双月周三） 单个汇报自动 这里只做一次记录 留存
                    $detail['dotarget'] = preg_replace('# #', '', $data[$j][15]);//年度完成工作量自评 自己预估(百分比)
                    $detail['investment'] = preg_replace('# #', '', $data[$j][17]);//总投资金额

                    $detail['file'] = $filePath;
                    $detail['hbdate'] =preg_replace('# #', '', $data[$j][21]);//汇报日期 excel录入的 只做记录
                   // $detail['remark'] = preg_replace('# #', '', $data[$j][22]); //备注
                    $detail['areacode'] = preg_replace('# #', '', $data[$j][22]); //备注

                    $detail['ctime'] = time();

                    if ($isDetails) {
                        //更新时间
                        $detail['utime'] = time();
                        //更新资金表
                        $payment['unit_id'] = $unit_id;
                        $payment['a_id'] = $assignment_id;
                        $payment['utime'] = time();
                        $p = preg_replace('# #', '', $data[$j][19]);//转移支付
                        if(empty($p)){
                            $pi = 0;
                        }else{
                            $pi = $p;
                        }
                        $payment['payment'] = $pi;

                        $e = preg_replace('# #', '', $data[$j][18]);//自筹
                        if(empty($e)){
                            $ei = 0;
                        }else{
                            $ei = $e;
                        }
                        $payment['expense'] = $ei;

                        $o = preg_replace('# #', '', $data[$j][20]);//其他
                        if(empty($o)){
                            $oi = 0;
                        }else{
                            $oi = $o;
                        }
                        $payment['other'] = $oi;

                        //更新
                        db('payment')->where(['id'=>$details['p_id']])->update($payment);
                        db('project')->where('id', $details['id'])->update($detail);
                    } else {
                        //新增项目
                        //payment资金来源表
                        $payment['unit_id'] = $unit_id;
                        $payment['a_id'] = $assignment_id;
                        $payment['ctime'] = time();

                        $p = preg_replace('# #', '', $data[$j][19]);//转移支付
                        if(empty($p)){
                            $pi = 0;
                        }else{
                            $pi = $p;
                        }
                        $payment['payment'] = $pi;

                        $e = preg_replace('# #', '', $data[$j][18]);//自筹
                        if(empty($e)){
                            $ei = 0;
                        }else{
                            $ei = $e;
                        }
                        $payment['expense'] = $ei;

                        $o = preg_replace('# #', '', $data[$j][20]);//其他
                        if(empty($o)){
                            $oi = 0;
                        }else{
                            $oi = $o;
                        }
                        $payment['other'] = $oi;

                        $payment_id = Db::table('fa_payment')->insertGetId($payment);
                        $detail['p_id'] = $payment_id;
                        $inres = db('project')->insert($detail);
                        if(!$inres){
                            Db::rollback();
                            $this->error("数据导入失败");
                        }
                    }

                }
            }
            Db::commit();

        } catch (\Exception $e) {
            $this->error($e->getMessage());
            Db::rollback();
        }

        $this->success('数据导入成功');
    }




}
